#!/use/bin/python
# coding=utf-8
# 主动服务 服务订阅数量 设置 净新增及 流失用户数
import redis
import datetime
import pymysql
from datetime import date, timedelta

from dbutils.pooled_db import PooledDB

# proactive_service_conf 数据源
def getConfConnection():
    # 开发环境
    #pool = PooledDB(pymysql, 1, host='172.20.135.96', user='pushdb', passwd='SkYWOrTh$TcOs',
    #                db='proactive_service_conf', port=3306)  # 1为连接池里的最少连接数
    # 测试环境
    pool = PooledDB(pymysql, 1, host='172.20.154.103', user='test_dmp', passwd='DghHC3lFM1KzT3ZJ',
                    db='proactive_service_conf', port=3307)  # 1为连接池里的最少连接数
    # pool = PooledDB(pymysql,1,host='127.0.0.1',user='root',passwd='root',db='life_assistant_data',port=3306) # 5为连接池里的最少连接数
    conn = pool.connection()
    cur = conn.cursor()
    return conn, cur

# proactive_service_data 数据源
def getDataConnection():
    # 开发环境
    #pool = PooledDB(pymysql, 1, host='172.20.151.80', user='pushdb', passwd='SkYWOrTh$TcOs',
    #                db='proactive_service_data',port=3306)  # 1为连接池里的最少连接数
    pool = PooledDB(pymysql, 1, host='172.20.154.103', user='test_dmp', passwd='DghHC3lFM1KzT3ZJ',
                    db='proactive_service_data', port=3407)  # 1为连接池里的最少连接数
    # pool = PooledDB(pymysql,1,host='127.0.0.1',user='root',passwd='root',db='life_assistant_data',port=3306) # 5为连接池里的最少连接数
    conn = pool.connection()
    cur = conn.cursor()
    return conn, cur

# redis
def getReidsConnection():
    redis_info = {
        "host": "172.20.151.90",
        "password": "Coocaa2022",
        "port": 6379,
        "db": 8
    }

    r = redis.Redis(**redis_info, decode_responses=True)
    return r

#根据传入的SQL 返回执行SQL返回的数量
def selectNumBySql(sql,conf=2):
    if conf == 1:
        conn, cur = getConfConnection()
    else:
        conn, cur = getDataConnection()
    #print(sql)
    cur.execute(sql)
    numResult = cur.fetchone()
    num = 0
    if numResult is not None:
        num = numResult[0]
    return num

# 记录 主动服务-各服务订阅用户数 analysis_subscribe_number
#date 当天  serId 服务 execute_flag 是否执行一次 0否 1是
def insertSubscribeNumber(toDay,serId,execute_flag):
    try:
        if execute_flag == 1:
            # redis key
            redisStr = 'V1'
            # 日志文案
            logStr = '执行一次'
        else:
            redisStr = 'V2'
            logStr = '无需执行一次'
        conn, cur = getDataConnection()
        r = getReidsConnection()
        #判断 analysis_subscribe_number 表是否已经存在当天 的数据
        countNum = selectNumBySql(
            f"select count(1) num from analysis_subscribe_number t where 1=1 and t.service_key ='{serId}' and t.ref_date =  '{toDay}' and t.execute_flag = {execute_flag}")
        #获取已经计算好的每天变化量
        newNum = selectNumBySql(
            f"select new_num from analysis_subscribe_number t where t.service_key ='{serId}' and t.ref_date =  '{toDay}' and t.execute_flag = {execute_flag}")
        #净新增数量
        increaseKey = f"LOG_DEVICE_NEW_ACTIVE_{serId.upper()}_{toDayYMD}"
        increaseNum = r.scard(increaseKey)
        #流失数量
        reduceKey = f"LOG_DEVICE_REDUCE_ACTIVE_{serId.upper()}_{toDayYMD}"
        reduceNum = r.scard(reduceKey)
        serName = serIdToName(serId)

        if countNum == 0:
            logDetail = f"{logStr}:{toDay}日新增-订阅{serName}设备量变化数为{newNum},净新增数为{increaseNum},流失数为{reduceNum}"
            print(logDetail)
            # 插入
            insertSql = f"""insert into analysis_subscribe_number(new_num,increase_num,reduce_num,ref_date,service_key,execute_flag) 
                value({newNum},{increaseNum},{reduceNum},'{toDay}','{serId}',{execute_flag})"""
            cur.execute(insertSql)
        else:
            logDetail = f"{logStr}:{toDay}日更新-订阅{serName}设备量变化数为{newNum},净新增数为{increaseNum},流失数为{reduceNum}"
            print(logDetail)
            # 更新
            updateSql = f"""
                update analysis_subscribe_number set new_num = {newNum} ,increase_num = {increaseNum},reduce_num = {reduceNum} ,update_time = now()  where
                    ref_date = '{toDay}' and service_key ='{serId}' and execute_flag = {execute_flag} 
            """
            cur.execute(updateSql)
        saveLog(logDetail, toDay)
        conn.commit()
    except Exception as e:
        print(e)
    finally:
        cur.close()
        conn.close()

#根据serId 获取服务中文名
def serIdToName(serId):
    if serId =='zjkj':
        serName = "早间开机"
    elif serId =='jkzx':
        serName = "健康作息"
    elif serId =='yjyl':
        serName = "夜间音量"
    elif serId =='zjtx':
        serName = "追剧提醒"
    elif serId =='sjtx':
        serName = "睡觉提醒"
    elif serId =='zmgn':
        serName = "助眠功能"
    else:
        serName = "总体"
    return serName


# 获取前1天或N天的日期，beforeOfDay=1：前1天；beforeOfDay=N：前N天
def getdate(beforeOfDay):
    today = datetime.datetime.now()
    # 计算偏移量
    offset = datetime.timedelta(days=-beforeOfDay)
    # 获取想要的日期的时间
    re_date = (today + offset).strftime('%Y-%m-%d')
    return re_date

# 获取前1天或N天的日期，beforeOfDay=1：前1天；beforeOfDay=N：前N天 格式为YMD
def getdateYMD(beforeOfDay):
    today = datetime.datetime.now()
    # 计算偏移量
    offset = datetime.timedelta(days=-beforeOfDay)
    # 获取想要的日期的时间
    re_date = (today + offset).strftime('%Y%m%d')
    return re_date


#保存日志
def saveLog(detail,ref_date):
    try:
        conn, cur = getDataConnection()
        jobId = 39
        jobName = '核心指标-生活助手-订阅用户数(无需执行一次)'
        tableName = 'analysis_subscribe_number'
        # 插入日志表
        saveLogSql = f"""insert into log_xxljob_analysis (job_id,job_name,detail,table_name,ref_date) 
                                value({jobId},'{jobName}','{detail}','{tableName}','{ref_date}')"""
        cur.execute(saveLogSql)
        conn.commit()
    except Exception as e:
        print(e)
    finally:
        cur.close()
        conn.close()

# 执行各服务每日订阅次数  execute_flag : 是否执行一次 1是 0 否
def serviceSubscribeNumber(execute_flag):
    insertSubscribeNumber(toDay, 'zjkj', execute_flag)
    insertSubscribeNumber(toDay, 'jkzx', execute_flag)
    insertSubscribeNumber(toDay, 'yjyl', execute_flag)
    insertSubscribeNumber(toDay, 'zjtx', execute_flag)
    insertSubscribeNumber(toDay, 'sjtx', execute_flag)
    insertSubscribeNumber(toDay, 'all', execute_flag)

def zmgnSubscribeNumber():
    insertSubscribeNumber(toDay, 'zmgn', 0)


if __name__ == '__main__':
    #today = todayYMD()
    toDay = getdate(1)
    toDayYMD = getdateYMD(1)
    #today = "2021-12-13"
    print (f"{toDay} 日期,执行<主动服务订阅用户数-更新净新增数及流失用户数>" )
    serviceSubscribeNumber(0)
    zmgnSubscribeNumber()
    serviceSubscribeNumber(1)

